﻿using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace CATL.PrintMarkingServer.DAL.DBConnectionHelper
{
    public class SqlServerClient : IDisposable
    {
        public SqlServerClient()
        {

        }

        public SqlServerClient(string connectString)
        {
            Connect(connectString);
        }

        public SqlConnection? Connection { get; private set; }

        public void Connect(string connectString)
        {
            Connection = new SqlConnection(connectString);
            Connection.Open();
        }

        public Task<int> WriteAsync(string commandString)
        {
            if (Connection is null)
            {
                throw new InvalidOperationException("Sqlite database is not connected.");
            }

            var command = Connection.CreateCommand();

            command.CommandText = commandString;
            return command.ExecuteNonQueryAsync();
        }

        public async Task<List<string>> ReadAsStringAsync(string queryString)
        {
            if (Connection is null)
            {
                throw new InvalidOperationException("Sqlite database is not connected.");
            }

            var command = Connection.CreateCommand();
            command.CommandText = queryString;
            using var reader = command.ExecuteReader();
            List<string> rows = new List<string>();

            while (await reader.ReadAsync())
            {
                var count = reader.FieldCount;
                var stringBuilder = new StringBuilder() ;

                for (int columnIndex = 0; columnIndex < count; columnIndex++)
                {
                    var rowString = reader.GetValue(columnIndex);
                    stringBuilder.Append(rowString);
                    stringBuilder.Append(",");
                }

                // 移除末尾逗号。
                if (stringBuilder.ToString() != string.Empty)
                {
                    stringBuilder.Remove(stringBuilder.Length - 1, 1); 
                }

                rows.Add(stringBuilder.ToString());
            }

            return rows;
        }

        public void Dispose()
        {
            if (Connection?.State != System.Data.ConnectionState.Closed)
            {
                Connection?.Close();
            }
        }
    }
}
